/**
 * 
 */
package org.starsource.forum.server.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashSet;
import java.util.NoSuchElementException;
import java.util.Set;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import org.starsource.forum.client.exception.DatabaseException;
import org.starsource.forum.client.exception.UserNotFoundException;
import org.starsource.forum.server.dao.UserDao;
import org.starsource.forum.server.domain.User;

/**
 * @author james
 *
 */
public class UserDaoJdbc implements UserDao {
	
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static org.apache.log4j.Logger logger = org.apache.log4j.LogManager
	.getLogger(UserDaoJdbc.class.getName());

	private class GroupRowCallbackHandler implements RowCallbackHandler {
		private Set<User> userSet;
		
		public void processRow(ResultSet rs) throws SQLException {
			User user = new User();
			user.setId(rs.getInt("id"));
			user.setAccount(rs.getString("account"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setDirectory(rs.getString("directory"));

			userSet.add(user);
		}
		
		public GroupRowCallbackHandler(Set<User> userSet) {
			this.userSet = userSet;
		}

	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#delete(org.starsource.forum.server.domain.User)
	 */
	@Override
	public void delete(final User user) throws DatabaseException {
		try {
			final String sql = "DELETE FROM t_user WHERE id = ?";
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, user.getId());
					return ps;
				}
			});
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#getUser(int)
	 */
	@Override
	public User getUser(int id) throws UserNotFoundException, DatabaseException {
		try {
			String sql = "SELECT * FROM t_user WHERE id = ? ";
			final Set<User> userSet = new LinkedHashSet<User>();
			jdbcTemplate.query(sql, new Object[] {id}, new GroupRowCallbackHandler(userSet));
			return userSet.iterator().next();
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new UserNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#getUser(java.lang.String)
	 */
	@Override
	public User getUser(String account) throws UserNotFoundException,
			DatabaseException {
		try {
			String sql = "SELECT * FROM t_user WHERE account = ? ";
			final Set<User> userSet = new LinkedHashSet<User>();
			jdbcTemplate.query(sql, new Object[] {account}, new GroupRowCallbackHandler(userSet));
			return userSet.iterator().next();
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new UserNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#getUser(java.lang.String, java.lang.String)
	 */
	@Override
	public User getUser(String account, String password)
			throws UserNotFoundException, DatabaseException {
		try {
			String sql = "SELECT * FROM t_user WHERE account = ? and password = ?";
			final Set<User> userSet = new LinkedHashSet<User>();
			jdbcTemplate.query(sql, new Object[] {account, password}, new GroupRowCallbackHandler(userSet));
			return userSet.iterator().next();
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new UserNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#save(org.starsource.forum.server.domain.User)
	 */
	@Override
	public void save(final User user) throws DatabaseException {
		final String sql = "INSERT INTO t_user(account, password, name, directory) VALUES(?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
				
				ps.setString(1, user.getAccount());
				ps.setString(2, user.getPassword());
				ps.setString(3, user.getName());
				ps.setString(4, user.getDirectory());
				return ps;
			}
		}, keyHolder);
		user.setId(keyHolder.getKey().intValue());
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.UserDao#update(org.starsource.forum.server.domain.User)
	 */
	@Override
	public void update(final User user) throws DatabaseException {
		final String sql = "UPDATE t_user set account = ?, password = ?, name = ?, directory = ? WHERE id = ?";
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, user.getAccount());
				ps.setString(2, user.getPassword());
				ps.setString(3, user.getName());
				ps.setString(4, user.getDirectory());
				ps.setInt(5, user.getId());
				return ps;
			}
		});
	}

}
